﻿using System;
using System.Collections.Generic;

using System.Web;
using System.Configuration;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using Slja.DBUtility;


namespace Slja.DAL
{
    public class PrjDesign 
    {
        public PrjDesign()
        {
            //DbHelperSQL.connectionString = SqlServerHelper.connectionString;
        }
        public DataTable GetPrjData(string strWhere)
        {
            string sql = "SELECT [ID],[Region],[Shop],[PrjID],[PrjName],[Designer],[DesignDate],year([DesignDate]) as [Year] FROM [dbo].[PrjMain] ";
            if (strWhere.Trim() != "")
            {
                sql = sql + " where " + strWhere;
            }
            DataTable dt = DbHelperSQL.Query(sql).Tables[0];
            //DataTable dt = SqlServerHelper.Query(sql).Tables[0];
            return dt;
        }
       
          
            #region  Method
        public bool Exist(string PrjID)
        {
              StringBuilder strSql = new StringBuilder();
                strSql.Append("select top(1) 1 from PrjMain ");
                strSql.Append(" where PrjID=@PrjID");
                SqlParameter[] parameters = {
					new SqlParameter("@PrjID", SqlDbType.NVarChar,20)
			    };
                parameters[0].Value = PrjID;               
                object obj = DbHelperSQL.GetSingle(strSql.ToString(),parameters);
                if (obj != null && obj != DBNull.Value)
                {
                    return true;
                }
                else
                {
                    return false;
                }
        }

            /// <summary>
            /// 增加一条数据,及其子表数据
            /// </summary>
            public int Add(Slja.Model.PrjMain model)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("insert into PrjMain(");
                strSql.Append("Region,Shop,PrjID,PrjName,Designer,DesignDate)");
                strSql.Append(" values (");
                strSql.Append("@Region,@Shop,@PrjID,@PrjName,@Designer,@DesignDate)");
                strSql.Append(";set @ReturnValue= @@IDENTITY");
                SqlParameter[] parameters = {
					new SqlParameter("@Region", SqlDbType.NVarChar,80),
					new SqlParameter("@Shop", SqlDbType.NVarChar,100),
					new SqlParameter("@PrjID", SqlDbType.NVarChar,50),
					new SqlParameter("@PrjName", SqlDbType.NVarChar,100),
					new SqlParameter("@Designer", SqlDbType.NVarChar,50),
					new SqlParameter("@DesignDate", SqlDbType.NVarChar,50),
					new SqlParameter("@ReturnValue",SqlDbType.Int)};
                parameters[0].Value = model.Region;
                parameters[1].Value = model.Shop;
                parameters[2].Value = model.PrjID;
                parameters[3].Value = model.PrjName;
                parameters[4].Value = model.Designer;
                parameters[5].Value = model.DesignDate;
                parameters[6].Direction = ParameterDirection.Output;

                List<CommandInfo> sqllist = new List<CommandInfo>();
                CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters);
                sqllist.Add(cmd);
                StringBuilder strSql2;
                foreach (Slja.Model.PrjDesignDetail models in model.PrjDesignDetails)
                {
                    strSql2 = new StringBuilder();
                    strSql2.Append("insert into PrjDesignDetail(");
                    strSql2.Append("PID,DesignXml,ImgPath)");
                    strSql2.Append(" values (");
                    strSql2.Append("@PID,@DesignXml,@ImgPath)");
                    SqlParameter[] parameters2 = {
						new SqlParameter("@PID", SqlDbType.Int,4),
						new SqlParameter("@DesignXml", SqlDbType.NVarChar,-1),
                        new SqlParameter("@ImgPath", SqlDbType.NVarChar,-1)                    
                                                 };
                   // parameters2[0].Value = models.PID;
                    parameters2[0].Direction = ParameterDirection.InputOutput;
                    parameters2[1].Value =(object) models.DesignXml;
                    parameters2[2].Value = (object)models.ImgPath;

                    cmd = new CommandInfo(strSql2.ToString(), parameters2);
                    sqllist.Add(cmd);
                }
                DbHelperSQL.ExecuteSqlTranWithIndentity(sqllist);
                return (int)parameters[6].Value;
            }
            /// <summary>
            /// 更新一条数据
            /// </summary>
            public void Update(Slja.Model.PrjMain model)
            {
                //更新主表
                StringBuilder strSql = new StringBuilder();
                strSql.Append("update PrjMain set ");
                strSql.Append("Region=@Region,");
                strSql.Append("Shop=@Shop,");
                strSql.Append("PrjID=@PrjID,");
                strSql.Append("PrjName=@PrjName,");
                strSql.Append("Designer=@Designer,");
                strSql.Append("DesignDate=@DesignDate");
                strSql.Append(" where ID=@ID");
                SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4),
					new SqlParameter("@Region", SqlDbType.NVarChar,80),
					new SqlParameter("@Shop", SqlDbType.NVarChar,100),
					new SqlParameter("@PrjID", SqlDbType.NVarChar,50),
					new SqlParameter("@PrjName", SqlDbType.NVarChar,100),
					new SqlParameter("@Designer", SqlDbType.NVarChar,50),
					new SqlParameter("@DesignDate", SqlDbType.NVarChar,50)};
                parameters[0].Value = model.ID;
                parameters[1].Value = model.Region;
                parameters[2].Value = model.Shop;
                parameters[3].Value = model.PrjID;
                parameters[4].Value = model.PrjName;
                parameters[5].Value = model.Designer;
                parameters[6].Value = model.DesignDate;

                List<CommandInfo> sqllist = new List<CommandInfo>();
                CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters);
                sqllist.Add(cmd);
                
                //删除从表
                StringBuilder strSql1 = new StringBuilder();
                strSql1.Append("delete from PrjDesignDetail ");
                strSql1.Append("where PID=@PID");

                SqlParameter[] parameters1 = {
						new SqlParameter("@PID", SqlDbType.Int,4)};
                
                parameters1[0].Value = model.ID;
                CommandInfo cmd1 = new CommandInfo(strSql1.ToString(), parameters1);
                sqllist.Add(cmd1);

                //插入从表
                StringBuilder strSql2;
                if( model.PrjDesignDetails !=null &&  model.PrjDesignDetails.Count>0)
                {
                    strSql2 = new StringBuilder();
                    strSql2.Append("insert into PrjDesignDetail(PID,DesignXml,ImgPath) values(@PID,@DesignXml,@ImgPath) ");                   
               
                    SqlParameter[] parameters2 = {
						new SqlParameter("@PID", SqlDbType.Int,4),
						new SqlParameter("@DesignXml", SqlDbType.NVarChar,-1),
                        new SqlParameter("@ImgPath", SqlDbType.NVarChar,-1)
                    };
                    // parameters2[0].Value = models.PID;
                    parameters2[0].Value = model.ID;
                    parameters2[1].Value = model.PrjDesignDetails[0].DesignXml;
                    parameters2[2].Value = model.PrjDesignDetails[0].ImgPath;

                    CommandInfo cmd2 = new CommandInfo(strSql2.ToString(), parameters2);
                    sqllist.Add(cmd2);
                }
                DbHelperSQL.ExecuteSqlTranWithIndentity(sqllist);              
            }

            /// <summary>
            /// 删除一条数据，及子表所有相关数据
            /// </summary>
            public bool Delete(int ID)
            {
                List<CommandInfo> sqllist = new List<CommandInfo>();
                StringBuilder strSql2 = new StringBuilder();
                strSql2.Append("delete PrjDesignDetail ");
                strSql2.Append(" where PID=@PID ");
                SqlParameter[] parameters2 = {
					new SqlParameter("@PID", SqlDbType.Int,4)};
                parameters2[0].Value = ID;

                CommandInfo cmd = new CommandInfo(strSql2.ToString(), parameters2);
                sqllist.Add(cmd);
                StringBuilder strSql = new StringBuilder();
                strSql.Append("delete PrjMain ");
                strSql.Append(" where ID=@ID");
                SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)
			};
                parameters[0].Value = ID;

                cmd = new CommandInfo(strSql.ToString(), parameters);
                sqllist.Add(cmd);
                int rowsAffected = DbHelperSQL.ExecuteSqlTran(sqllist);
                if (rowsAffected > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            /// <summary>
            /// 删除一条数据
            /// </summary>
            public bool DeleteList(string IDlist)
            {
                List<string> sqllist = new List<string>();
                StringBuilder strSql2 = new StringBuilder();
                strSql2.Append("delete from PrjDesignDetail ");
                strSql2.Append(" where PID in (" + IDlist + ")  ");
                sqllist.Add(strSql2.ToString());
                StringBuilder strSql = new StringBuilder();
                strSql.Append("delete from PrjMain ");
                strSql.Append(" where ID in (" + IDlist + ")  ");
                sqllist.Add(strSql.ToString());
                int rowsAffected = DbHelperSQL.ExecuteSqlTran(sqllist);
                if (rowsAffected > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }


            /// <summary>
            /// 得到一个对象实体
            /// </summary>
            public Slja.Model.PrjMain GetModel(int ID)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select ID,Region,Shop,PrjID,PrjName,Designer,DesignDate from PrjMain ");
                strSql.Append(" where ID=@ID");
                SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)
			};
                parameters[0].Value = ID;

                Slja.Model.PrjMain model = new Slja.Model.PrjMain();
                DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    #region  父表信息
                    if (ds.Tables[0].Rows[0]["ID"] != null && ds.Tables[0].Rows[0]["ID"].ToString() != "")
                    {
                        model.ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                    }
                    if (ds.Tables[0].Rows[0]["Region"] != null && ds.Tables[0].Rows[0]["Region"].ToString() != "")
                    {
                        model.Region = ds.Tables[0].Rows[0]["Region"].ToString();
                    }
                    if (ds.Tables[0].Rows[0]["Shop"] != null && ds.Tables[0].Rows[0]["Shop"].ToString() != "")
                    {
                        model.Shop = ds.Tables[0].Rows[0]["Shop"].ToString();
                    }
                    if (ds.Tables[0].Rows[0]["PrjID"] != null && ds.Tables[0].Rows[0]["PrjID"].ToString() != "")
                    {
                        model.PrjID = ds.Tables[0].Rows[0]["PrjID"].ToString();
                    }
                    if (ds.Tables[0].Rows[0]["PrjName"] != null && ds.Tables[0].Rows[0]["PrjName"].ToString() != "")
                    {
                        model.PrjName = ds.Tables[0].Rows[0]["PrjName"].ToString();
                    }
                    if (ds.Tables[0].Rows[0]["Designer"] != null && ds.Tables[0].Rows[0]["Designer"].ToString() != "")
                    {
                        model.Designer = ds.Tables[0].Rows[0]["Designer"].ToString();
                    }
                    if (ds.Tables[0].Rows[0]["DesignDate"] != null && ds.Tables[0].Rows[0]["DesignDate"].ToString() != "")
                    {
                        model.DesignDate = ((DateTime)ds.Tables[0].Rows[0]["DesignDate"]).ToString("yyyy-MM-dd");
                    }
                    #endregion  父表信息end

                    #region  子表信息
                    StringBuilder strSql2 = new StringBuilder();
                    strSql2.Append("select ID,PID,DesignXml,ImgPath from PrjDesignDetail ");
                    strSql2.Append(" where PID=@ID ");
                    SqlParameter[] parameters2 = {
					new SqlParameter("@ID", SqlDbType.Int,4)};
                    parameters2[0].Value = ID;

                    DataSet ds2 = DbHelperSQL.Query(strSql2.ToString(), parameters2);
                    if (ds2.Tables[0].Rows.Count > 0)
                    {
                        #region  子表字段信息
                        int i = ds2.Tables[0].Rows.Count;
                        List<Slja.Model.PrjDesignDetail> models = new List<Slja.Model.PrjDesignDetail>();
                        Slja.Model.PrjDesignDetail modelt;
                        for (int n = 0; n < i; n++)
                        {
                            modelt = new Slja.Model.PrjDesignDetail();
                            if (ds2.Tables[0].Rows[n]["ID"] != null && ds2.Tables[0].Rows[n]["ID"].ToString() != "")
                            {
                                modelt.ID = int.Parse(ds2.Tables[0].Rows[n]["ID"].ToString());
                            }
                            if (ds2.Tables[0].Rows[n]["PID"] != null && ds2.Tables[0].Rows[n]["PID"].ToString() != "")
                            {
                                modelt.PID = int.Parse(ds2.Tables[0].Rows[n]["PID"].ToString());
                            }
                            if (ds2.Tables[0].Rows[n]["DesignXml"] != null && ds2.Tables[0].Rows[n]["DesignXml"].ToString() != "")
                            {
                                modelt.DesignXml = ds2.Tables[0].Rows[n]["DesignXml"].ToString();
                            }
                            if (ds2.Tables[0].Rows[n]["ImgPath"] != null && ds2.Tables[0].Rows[n]["ImgPath"].ToString() != "")
                            {
                                modelt.ImgPath = ds2.Tables[0].Rows[n]["ImgPath"].ToString();
                            }
                            models.Add(modelt);
                        }
                        model.PrjDesignDetails = models;
                        #endregion  子表字段信息end
                    }
                    #endregion  子表信息end

                    return model;
                }
                else
                {
                    return null;
                }
            }

            #endregion  Method
  
    }
}